# Eli Sowash
# IST554, Worksheet 7
# 02-19-2013

import sqlite3






def main():
    
    
    db = sqlite3.connect('insurance.sqlite')
    print('Question 1: Display a list of customers who live in small cities, with populations less than 500,000')
    output = db.execute('SELECT CONTRACTS.CUSTOMER_NAME, CONTRACTS.CUSTOMER_CITY FROM CONTRACTS JOIN CITIES ON CONTRACTS.CUSTOMER_CITY = CITIES.IDENT WHERE CITIES.POPULATION < 500000 ORDER BY CONTRACTS.CUSTOMER_CITY')
        
    for row in output:
        print(row)
        
    print('Question 2: Print the list of luxury cars under contract, with the VIN, Make, and Model')
    output = db.execute('SELECT CONTRACTS.CAR_IDENT, CARS.MAKE, CARS.MODEL FROM CONTRACTS JOIN CARS ON CONTRACTS.CAR_IDENT = CARS.IDENT WHERE CARS.LUXURY = "Y"')   
    
    for row in output:
        print(row)
        
    print('Question 3: Set the base price in the contracts table to 1000, for any contract that cover that has a contract class of 10 or above')
    print('First, display the records that meet the criteria (contract class of 10 or above')
    
    output1 = db.execute('SELECT CUSTOMER_NAME, CONTRACT_CLASS, BASE_PRICE FROM CONTRACTS WHERE CONTRACT_CLASS > 9')   
    
    for row in output1:
       print(row)
    
    print('Then, update the base price, and print the same query again')    
    
    db.execute('UPDATE CONTRACTS SET BASE_PRICE = 1000 WHERE CONTRACT_CLASS > 9')   
    
    output2 = db.execute('SELECT CUSTOMER_NAME, CONTRACT_CLASS, BASE_PRICE FROM CONTRACTS WHERE CONTRACT_CLASS > 9')   
    
    for row in output2:
       print(row)

if __name__ == "__main__": main()
